/**
 * 转换成目标SQL
 * @param {string} sourceSql
 */
export function dbTransfer(sourceSql) {
  let sourceSqlArray = []
  if (sourceSql.includes("\r")) {
    sourceSqlArray = sourceSql.split("\r\n")
  } else {
    sourceSqlArray = sourceSql.split("\n")
  }
  if (sourceSqlArray.length <= 0) {
    return
  }
  let targetSqlArray = []
  for (let i = 0; i < sourceSqlArray.length; i++) {
    let mainSql = ""
    let beginFlag = false
    let rowSql = sourceSqlArray[i]
    while (rowSql) {
      const createRegex = new RegExp("CREATE\\s*TABLE\\s*`[^`]+`\\s*\\(.*", "i")
      if (createRegex.test(rowSql)) {
        beginFlag = true
      }
      if (beginFlag) {
        mainSql = mainSql.concat(rowSql)
      }
      const commitRegex = new RegExp(
        ".*?\\).*?COMMENT\\s*=\\s*'[^']+'.*?;.*?$",
        "i"
      )
      if (commitRegex.test(rowSql)) {
        break
      }
      i += 1
      rowSql = sourceSqlArray[i]
    }
    // 开始分析表字段
    if (
      new RegExp(
        "\\s*CREATE\\s*TABLE\\s*`[^`]+`\\s*\\(.*\\).*COMMENT\\s*=\\s*'[^']+'.*;.*$",
        "i"
      ).test(mainSql)
    ) {
      const targetRegex =
        /\s*CREATE\s*TABLE\s*`([^`]+)`\s*\((.*)\).*COMMENT\s*=\s*'([^']+)'.*;.*/i
      let targetMatcher = mainSql.match(targetRegex)
      const tableName = targetMatcher[1].trim()
      const tableComment = targetMatcher[3].trim()
      const tableColumn = targetMatcher[2].trim()
      let primaryList = []
      console.log("开始解析表名: %s, 表注释: %s", tableName, tableComment)
      targetMatcher = tableColumn.match(
        ".*PRIMARY\\s+KEY\\s*\\(([^\\(\\)]+)\\).*"
      )
      if (targetMatcher.length > 0) {
        const primaryKeysRegex = new RegExp("\\s*`\\s*([^`]+?)\\s*`\\s*", "g")
        targetMatcher = primaryKeysRegex.exec(targetMatcher[1].trim())
        while (targetMatcher != null) {
          primaryList.push(targetMatcher[1].trim())
          targetMatcher = primaryKeysRegex.exec(targetMatcher[1].trim())
        }
      }
      const tableColumnRegex = new RegExp(
        "\\s*`([^`]+)`\\s*([a-zA-Z]+?)(\\s+|\\s*\\([\\d\\s,]+\\))([^`]*?)COMMENT\\s*'([^`]+)'\\s*(?:,|\\)|$)",
        "gi"
      )
      targetMatcher = tableColumnRegex.exec(tableColumn)
      let tableInfos = {}
      tableInfos.tableName = tableName
      tableInfos.tableComment = tableComment
      tableInfos.primaryIds = []
      tableInfos.commonFields = []
      tableInfos.fields = []
      while (targetMatcher != null) {
        let tableInfo = {}
        tableInfo.fieldName = targetMatcher[1].trim()
        tableInfo.fieldLength = targetMatcher[3]
          .trim()
          .replace("(", "")
          .replace(")", "")
        tableInfo.fieldComment = targetMatcher[5].trim()
        const extInfo = targetMatcher[4]
        tableInfo.fieldNotNull = extInfo.match(".*NOT\\s*NULL.*") ? true : false
        tableInfo.fieldAutoInc = extInfo.match(".*AUTO_INCREMENT.*")
          ? true
          : false
        const extInfoMatch = extInfo.match("DEFAULT\\s+([^\\s]+)\\s+")
        if (extInfoMatch) {
          tableInfo.fieldDefaultValue = extInfoMatch[1]
        }
        tableInfo.fieldType = targetMatcher[2].trim().toLocaleLowerCase()
        // 设置类型
        setPgTypeByMySQLType(tableInfo)
        targetMatcher = tableColumnRegex.exec(tableColumn)
        let primaryFlag = false
        for (let i = 0; i < primaryList.length; i++) {
          if (primaryList[i] == tableInfo.fieldName) {
            primaryFlag = true
            break
          }
        }
        if (primaryFlag) {
          tableInfos.primaryIds.push(tableInfo)
        } else {
          tableInfos.commonFields.push(tableInfos)
        }
        tableInfos.fields.push(tableInfo)
      }
      console.log(tableInfos)
      if (tableInfos != null) {
        if (
          tableInfos.fields.length <= 0 &&
          tableInfos.primaryIds.length <= 0
        ) {
          console.error("转换异常:【%s】表中没有字段", tableInfos.tableName)
        } else {
          // 构建目标SQL
          let targetSql = "\r\n"
          targetSql = targetSql + "------------------------------\r\n"
          targetSql =
            targetSql +
            "-- Table structure for " +
            tableInfos.tableName +
            "\r\n-- "
          targetSql =
            targetSql +
            new Intl.DateTimeFormat("zh", {
              timeStyle: "medium",
              dateStyle: "long"
            }).format() +
            "\r\n-- "
          targetSql =
            targetSql +
            tableInfos.tableComment +
            "\r\n------------------------------\r\n\r\n"
          for (const item of tableInfos.fields) {
            if (item.fieldAutoInc) {
              targetSql = targetSql + 'CREATE SEQUENCE "'
              targetSql =
                targetSql + tableInfos.tableName + "_" + item.fieldName
              targetSql += '_seq"\r\n'
              targetSql +=
                "    INCREMENT 1\r\n    START 1\r\n    MINVALUE 1\r\n    MAXVALUE 9223372036854775807\r\n    CACHE 1;\r\n\r\n"
            }
          }
          targetSql += 'DROP TABLE IF EXISTS "'
          targetSql =
            targetSql +
            tableInfos.tableName +
            '" CASCADE;\r\n\r\nCREATE TABLE "'
          targetSql = targetSql + tableInfos.tableName + '"('
          for (const item of tableInfos.fields) {
            targetSql += '\r\n	"'
            targetSql = targetSql + item.fieldName + '" ' + item.pgType
            if (item.fieldLength) {
              targetSql = targetSql + "(" + item.fieldLength + ")"
            }
            if (item.fieldCollate) {
              targetSql = targetSql + ' COLLATE "' + item.fieldCollate + '"'
            }
            if (item.fieldNotNull) {
              targetSql += " NOT NULL"
            }
            if (item.fieldDefaultValue) {
              targetSql = targetSql + " DEFAULT " + item.fieldDefaultValue
            } else if (item.fieldAutoInc) {
              targetSql =
                targetSql +
                " DEFAULT nextval('\"" +
                tableInfos.tableName +
                "_" +
                item.fieldName +
                "_seq\"'::regclass)"
            }
            targetSql += ","
          }
          if (tableInfos.primaryIds.length > 0) {
            targetSql =
              targetSql +
              "\r\n	CONSTRAINT " +
              tableInfos.tableName +
              "_pkey PRIMARY KEY ("
            for (const item of tableInfos.primaryIds) {
              targetSql = targetSql + '"' + item.fieldName + '",'
            }
            targetSql =
              targetSql.slice(0, targetSql.length - 1) +
              targetSql.slice(targetSql.length + 1)
            targetSql += ")"
          } else {
            targetSql =
              targetSql.slice(0, targetSql.length - 1) +
              targetSql.slice(targetSql.length + 1)
          }
          targetSql += "\r\n)\r\nWITH (\r\n    OIDS = FALSE\r\n)\r\n;\r\n\r\n"
          targetSql += 'COMMENT ON TABLE "'
          targetSql += tableInfos.tableName
          targetSql += "\" IS '"
          targetSql += tableInfos.tableComment
          targetSql += "';\r\n\r\n"
          for (const item of tableInfos.fields) {
            targetSql += 'COMMENT ON COLUMN "'
            targetSql += tableInfos.tableName
            targetSql += '"."'
            targetSql += item.fieldName
            targetSql += "\" IS '"
            targetSql += item.fieldComment
            targetSql += "';\r\n"
          }
          targetSqlArray.push(targetSql)
          console.log(targetSqlArray)
        }
      }
    }
  }
  return targetSqlArray
}
/**
 * 数据类型
 * @param {Object} tableInfo 表信息
 */
function setPgTypeByMySQLType(tableInfo) {
  switch (tableInfo.fieldType) {
    case "tinyint":
      tableInfo.pgType = "smallint"
      tableInfo.FJDBCType = "TINYINT"
      tableInfo.FModleType = "Byte"
      tableInfo.FModleTypeFull = "java.lang.Byte"
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue = tableInfo.fieldDefaultValue.replaceAll(
          "'",
          ""
        )
      }
      break
    case "smallint":
      tableInfo.pgType = "smallint"
      tableInfo.FJDBCType = "SMALLINT"
      tableInfo.FModleType = "Short"
      tableInfo.FModleTypeFull = "java.lang.Short"
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue = tableInfo.fieldDefaultValue.replaceAll(
          "'",
          ""
        )
      }
      break
    case "mediumint":
      tableInfo.pgType = "integer"
      tableInfo.FJDBCType = "INTEGER"
      tableInfo.FModleType = "Integer"
      tableInfo.FModleTypeFull = "java.lang.Integer"
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue = tableInfo.fieldDefaultValue.replaceAll(
          "'",
          ""
        )
      }
      break
    case "int":
      tableInfo.pgType = "integer"
      tableInfo.FJDBCType = "INTEGER"
      tableInfo.FModleType = "Integer"
      tableInfo.FModleTypeFull = "java.lang.Integer"
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue = tableInfo.fieldDefaultValue.replaceAll(
          "'",
          ""
        )
      }
      break
    case "integer":
      tableInfo.pgType = "integer"
      tableInfo.FJDBCType = "INTEGER"
      tableInfo.FModleType = "Integer"
      tableInfo.FModleTypeFull = "java.lang.Integer"
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue = tableInfo.fieldDefaultValue.replaceAll(
          "'",
          ""
        )
      }
      break
    case "bigint":
      tableInfo.pgType = "bigint"
      tableInfo.FJDBCType = "BIGINT"
      tableInfo.FModleType = "Long"
      tableInfo.FModleTypeFull = "java.lang.Long"
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue = tableInfo.fieldDefaultValue.replaceAll(
          "'",
          ""
        )
      }
      break
    case "bit":
      tableInfo.pgType = "boolean"
      tableInfo.FJDBCType = "BIT"
      tableInfo.FModleType = "Boolean"
      tableInfo.FModleTypeFull = "java.lang.Boolean"
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue = tableInfo.fieldDefaultValue.replaceAll(
          "'",
          ""
        )
      }
      break
    case "real":
      tableInfo.pgType = "real"
      tableInfo.FJDBCType = "DOUBLE"
      tableInfo.FModleType = "Double"
      tableInfo.FModleTypeFull = "java.lang.Double"
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue = tableInfo.fieldDefaultValue.replaceAll(
          "'",
          ""
        )
      }
      break
    case "double":
      tableInfo.pgType = "double precision"
      tableInfo.FJDBCType = "DOUBLE"
      tableInfo.FModleType = "Double"
      tableInfo.FModleTypeFull = "java.lang.Double"
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue = tableInfo.fieldDefaultValue.replaceAll(
          "'",
          ""
        )
      }
      break
    case "float":
      tableInfo.pgType = "real"
      tableInfo.FJDBCType = "REAL"
      tableInfo.FModleType = "Float"
      tableInfo.FModleTypeFull = "java.lang.Float"
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue = tableInfo.fieldDefaultValue.replaceAll(
          "'",
          ""
        )
      }
      break
    case "decimal":
      tableInfo.pgType = "numeric"
      tableInfo.FJDBCType = "DECIMAL"
      tableInfo.FModleType = "BigDecimal"
      tableInfo.FModleTypeFull = "	java.math.BigDecimal"
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue = tableInfo.fieldDefaultValue.replaceAll(
          "'",
          ""
        )
      }
      break
    case "numeric":
      tableInfo.pgType = "numeric"
      tableInfo.FJDBCType = "DECIMAL"
      tableInfo.FModleType = "BigDecimal"
      tableInfo.FModleTypeFull = "	java.math.BigDecimal"
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue = tableInfo.fieldDefaultValue.replaceAll(
          "'",
          ""
        )
      }
      break
    case "char":
      tableInfo.pgType = "character"
      tableInfo.FJDBCType = "CHAR"
      tableInfo.FModleType = "String"
      tableInfo.FModleTypeFull = "java.lang.String"
      tableInfo.fieldCollate = "default"
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue = tableInfo.fieldDefaultValue + "::bpchar"
      }
      break
    case "varchar":
      tableInfo.pgType = "character varying"
      tableInfo.FJDBCType = "VARCHAR"
      tableInfo.FModleType = "String"
      tableInfo.FModleTypeFull = "java.lang.String"
      tableInfo.fieldCollate = "default"
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue =
          tableInfo.fieldDefaultValue + "::character varying"
      }
      break
    case "date":
      tableInfo.pgType = "date"
      tableInfo.FJDBCType = "DATE"
      tableInfo.FModleType = "Date"
      tableInfo.FModleTypeFull = "java.util.Date"
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue =
          tableInfo.fieldDefaultValue + "::" + tableInfo.pgType
      }
      break
    case "time":
      tableInfo.pgType = "time with time zone"
      tableInfo.FJDBCType = "TIME"
      tableInfo.FModleType = "Date"
      tableInfo.FModleTypeFull = "java.util.Date"
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue =
          tableInfo.fieldDefaultValue + "::" + tableInfo.pgType
      }
      break
    case "timestamp":
      tableInfo.pgType = "timestamp with time zone"
      tableInfo.FJDBCType = "TIMESTAMP"
      tableInfo.FModleType = "Date"
      tableInfo.FModleTypeFull = "java.util.Date"
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue =
          tableInfo.fieldDefaultValue + "::" + tableInfo.pgType
      }
      break
    case "datetime":
      tableInfo.pgType = "timestamp with time zone"
      tableInfo.FJDBCType = "TIMESTAMP"
      tableInfo.FModleType = "Date"
      tableInfo.FModleTypeFull = "java.util.Date"
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue =
          tableInfo.fieldDefaultValue + "::" + tableInfo.pgType
      }
      break
    case "tinytext":
      tableInfo.pgType = "text"
      tableInfo.FJDBCType = "VARCHAR"
      tableInfo.FModleType = "String"
      tableInfo.FModleTypeFull = "java.lang.String"
      tableInfo.fieldCollate = "default"
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue =
          tableInfo.fieldDefaultValue + "::character varying"
      }
      break
    case "tinyblob":
      tableInfo.pgType = "bytea"
      tableInfo.FJDBCType = "BINARY"
      tableInfo.FModleType = "byte[]"
      tableInfo.FModleTypeFull = ""
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue =
          tableInfo.fieldDefaultValue + "::" + tableInfo.pgType
      }
      break
    case "blob":
      tableInfo.pgType = "bytea"
      tableInfo.FJDBCType = "LONGVARBINARY"
      tableInfo.FModleType = "byte[]"
      tableInfo.FModleTypeFull = ""
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue =
          tableInfo.fieldDefaultValue + "::" + tableInfo.pgType
      }
      break
    case "mediumblob":
      tableInfo.pgType = "bytea"
      tableInfo.FJDBCType = "LONGVARBINARY"
      tableInfo.FModleType = "byte[]"
      tableInfo.FModleTypeFull = ""
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue =
          tableInfo.fieldDefaultValue + "::" + tableInfo.pgType
      }
      break
    case "longblob":
      tableInfo.pgType = "bytea"
      tableInfo.FJDBCType = "LONGVARBINARY"
      tableInfo.FModleType = "byte[]"
      tableInfo.FModleTypeFull = ""
      tableInfo.fieldCollate = null
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue =
          tableInfo.fieldDefaultValue + "::" + tableInfo.pgType
      }
      break
    case "text":
      tableInfo.pgType = "text"
      tableInfo.FJDBCType = "LONGVARCHAR"
      tableInfo.FModleType = "String"
      tableInfo.FModleTypeFull = "java.lang.String"
      tableInfo.fieldCollate = "default"
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue =
          tableInfo.fieldDefaultValue + "::character varying"
      }
      break
    case "mediumtext":
      tableInfo.pgType = "text"
      tableInfo.FJDBCType = "LONGVARCHAR"
      tableInfo.FModleType = "String"
      tableInfo.FModleTypeFull = "java.lang.String"
      tableInfo.fieldCollate = "default"
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue =
          tableInfo.fieldDefaultValue + "::character varying"
      }
      break
    case "longtext":
      tableInfo.pgType = "text"
      tableInfo.FJDBCType = "LONGVARCHAR"
      tableInfo.FModleType = "String"
      tableInfo.FModleTypeFull = "java.lang.String"
      tableInfo.fieldCollate = "default"
      tableInfo.fieldLength = null
      if (tableInfo.fieldDefaultValue != null) {
        tableInfo.fieldDefaultValue =
          tableInfo.fieldDefaultValue + "::character varying"
      }
      break
    default:
      console.error("没有找到 [" + tableInfo.FDBType + "]对应的类型名！")
  }
}
